package com.tim.helper;

import lombok.extern.slf4j.Slf4j;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * jdbc插入，约8w/min
 * 存储过程插入，约60w/min
    create definer=`root`@`%` procedure `reconcile`.`createReconStatus`()
    BEGIN
 declare i int;
 declare j int;
 DECLARE EXIT HANDLER FOR SQLEXCEPTION
 BEGIN
     ROLLBACK;-- 回滚事务
     RESIGNAL; -- 重新抛出异常
 END;
 START TRANSACTION;
     set i=1;
     while i<10_0000 do
        set j=i*10;
        insert into ;
        set i=i+1;
     end while;
 COMMIT;
    END
    调用： call reconcile.createReconStatus()
 * */
@Slf4j
public class BatchInsertHelper {
    // 数据库连接信息
    String url = "jdbc:mysql://localhost:3306/your_database";
    String user = "your_username";
    String password = "your_password";
    /*选中要插入的表记录，修改填充属性为?，复制为insert，
        格式化sql，excel单元格设为文本，插入column列、value列
        对应设置填充值*/
    String sql = "INSERT INTO employees (first_name, last_name, email) VALUES (?, ?, ?)";
    int num=10*10000;
    int BATCH_SIZE = 500;

    @Test
    public void jdbcInsert() {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            // 加载 JDBC 驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 建立数据库连接
            connection = DriverManager.getConnection(url, user, password);

            // 关闭自动提交
            connection.setAutoCommit(false);

            // 创建 PreparedStatement 对象
            preparedStatement = connection.prepareStatement(sql);

            // 添加批量插入的数据
            int size=0;
            int insertTimes = 0;
            long start = System.currentTimeMillis();
            log.info("Batch insert started");
            for (int i = 0; i < num; i++) {
                fillStatement(preparedStatement);
                size++;
                preparedStatement.addBatch();
                if(size== BATCH_SIZE){
                    preparedStatement.executeBatch();
                    size=0;
                    insertTimes++;
                    if(insertTimes%20==0){
                        log.info("Batch insert executing, insertTimes={}", insertTimes);
                    }
                }
            }

            // 执行批量插入
            if(size!=0){
                preparedStatement.executeBatch();
            }

            // 提交事务
            connection.commit();

            // 输出结果
            System.out.println("Batch insert completed, cost="+(System.currentTimeMillis()-start));
        } catch (Exception e) {
           log.error("Batch insert error", e);
            // 发生异常时回滚事务
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    log.error("Batch insert rollback error", e);
                }
            }
        } finally {
            // 关闭资源
            try {
                if (preparedStatement != null) preparedStatement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                log.error("connection close error", e);
            }
        }
    }

    private static void fillStatement(PreparedStatement preparedStatement) throws SQLException {
        Object obj = null;
//        preparedStatement.setString(1, row[0]);
    }
}

